﻿Public Class PhieuXuatDAO
    Public Shared Function SelectAll() As DataTable
        Dim strSQL As String = "Select ct.IDCTPX as 'Mã CT Phiếu Xuất', sp.TenSp, ct.SoLuongXuat, ct.DonGia, ct.IDMaPX as 'Mã Phiếu Xuất' , kh.TenKH , px.NgayXuat From CTPHIEUXUAT ct, PHIEUXUAT px, KHACHHANG kh, SANPHAM sp where ct.IDMaPX = px.IDMaPX and ct.IDMaSP = sp.IDMaSP and ct.IDMaKH = kh.IDKH"
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    Public Shared Function SelectAllCTPX(ByVal iMaPhieuXuat As Integer) As DataTable
        Dim strSQL As String = "Select ct.IDCTPX as 'Mã CT Phiếu Xuất', sp.TenSp, ct.SoLuongXuat, ct.DonGia, ct.IDMaPX as 'Mã Phiếu Xuất' , kh.TenKH , px.NgayXuat From CTPHIEUXUAT ct, PHIEUXUAT px, KHACHHANG kh, SANPHAM sp where ct.IDMaPX = px.IDMaPX and ct.IDMaSP = sp.IDMaSP and ct.IDMaKH = kh.IDKH and px.IDMaPX = " & iMaPhieuXuat
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    'Insert chi tiet phieu xuat vao bang PHIEUXUAT
    Public Shared Function InsertPX(ByVal _px As PhieuXuatDTO) As Integer
        Dim strSQL As String = "Insert into PHIEUXUAT(NgayXuat) Values(" & _px.NgayXuat.Date & ")"
        SQLProvider.ExecNoneQuery(strSQL)

        strSQL = "Select Max(IDMaPX) from PHIEUXUAT"
        Dim dt As DataTable = SQLProvider.ExecQuery(strSQL)
        Return dt.Rows(0).Item(0)

    End Function

    'Insert chi tiet phieu xuat vao bang CTPHIEUXUAT
    Public Shared Sub InsertCTPX(ByVal _ctpx As CTPhieuXuatDTO)
        Dim strSQL As String = "Insert into CTPHIEUXUAT(IDMaSP, SoLuongXuat, DonGia,IDMaPX,IDMaKH) Values(" & _ctpx.MaSanPham & "," & _ctpx.SoLuongXuat & "," & _ctpx.DonGia & ", " & _ctpx.MaPhieuXuat & "," & _ctpx.MaKhachHang & " )"
        SQLProvider.ExecNoneQuery(strSQL)

    End Sub

    'Sua thong tin phieu nhap xuat
    Public Shared Sub UpdatePX(ByVal _px As PhieuXuatDTO)
        Dim strSQL As String = "Update PHIEUXUAT set NgayNhap = " & _px.NgayXuat.Date & "where IDMaPX = " & _px.MaPhieuXuat
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    'Sua thong tin chi tiet phieu xuat
    Public Shared Sub UpdateCTPX(ByVal _ctpx As CTPhieuXuatDTO)
        Dim strSQL As String = "Update CTPHIEUXUAT set IDMaSP = " & _ctpx.MaSanPham & ", SoLuongXuat = " & _ctpx.SoLuongXuat & ", DonGia = " & _ctpx.DonGia & ", IDMaPN = " & _ctpx.MaPhieuXuat & ", IDMaKH = " & _ctpx.MaKhachHang & " where IDMaPX = " & _ctpx.MaCTPhieuXuat
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    'Xoa thong tin phieu xuat
    Public Shared Sub DeletePX(ByVal _px As PhieuXuatDTO)
        Dim strSQL As String = "Delete from PHIEUXUAT where IDMaPX = " & _px.MaPhieuXuat
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    'Xoa thong tin chi tiet phieu xuat
    Public Shared Sub DeleteCTPX(ByVal _ctpx As CTPhieuXuatDTO)
        Dim strSQL As String = "Delete from CTPHIEUXUAT where IDCTPX = " & _ctpx.MaCTPhieuXuat
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    Public Shared Function SelectByID(ByVal iMaPhieuXuat As Integer) As PhieuXuatDTO
        Dim px As New PhieuXuatDTO

        Dim strSQL As String
        strSQL = "select * from PHIEUXUAT where IDMaPX = " & iMaPhieuXuat

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If
        px.MaPhieuXuat = dt.Rows(0).Item(0).ToString()
        px.NgayXuat = (dt.Rows(0).Item(1).ToString())

        Return px
    End Function

    Public Shared Function TotalValue(ByVal iMaPX As Integer) As Integer

        Dim iTotalValue As Integer = 0
        Dim strSQL As String

        strSQL = "select SUM(SoLuongXuat * DonGia) from CTPHIEUXUAT where IDMaPX = " & iMaPX
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        iTotalValue = dt.Rows(0).Item(0).ToString()
        Return iTotalValue
    End Function

    Public Shared Function SelectAllPX(ByVal iMaPX As Integer) As DataTable
        Dim strSQL As String
        strSQL = "select * from CTPHIEUXUAT where IDMaPX = " & iMaPX

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function
End Class
